# -*- coding:utf-8 -*-
"""
该脚本操作中央数据库
"""
import json
from .CONFIG import CS
from .mz_date import MZ_Date
import psycopg2

USER = CS['user']
PASSWORD = CS['password']
HOST = CS['host']
PORT = CS['port']



class CSDBManager(object):

    def __init__(self,db_name ='postgres',auto_create=True):
        super(CSDBManager, self).__init__()
        self.db_name = self.use_db(db_name,auto_create) #当前操作的数据库
        self.mzh = MZ_Date()

    def _default_connect(self):
        """
        默认连接postgres数据库
        :return: connect, cursor
        """
        try:
            connect = psycopg2.connect(database='postgres',
                                          user=USER,
                                          password=PASSWORD,
                                          host=HOST,
                                          port=PORT)
            cursor = connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:', cgdb_err)
            return None, None
        return connect, cursor

    def _init_post_db(self):
        """
        初始化192.168.1.53 postproduction数据库连接
        :param db_name: 默认连接自带的postgres数据库
        :return: connect, cursor
        """
        try:
            connect = psycopg2.connect(database=self.db_name,
                                          user=USER,
                                          password=PASSWORD,
                                          host=HOST,
                                          port=PORT)
            cursor = connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:', cgdb_err)
            return None, None
        return connect, cursor


    """
    -------------------------------------------------------------------------------
    数据库操作
    -------------------------------------------------------------------------------
    """
    def use_db(self,db_name,auto_create):
        """
        切换指定数据库
        :param db_name:
        :param auto_create:
        :return:
        """
        db = ''
        select_sql ="select * from pg_database where datname='{}'".format(db_name)
        sql ="create database {} ".format(db_name)
        connect, cursor = self._default_connect()
        try:
            cursor.execute(select_sql)
            connect.commit()
            result = cursor.fetchone()
            if not result and auto_create:
                connect.autocommit =True
                cursor.execute(sql)
                connect.autocommit =False
                db = db_name
                print('创建成功')
            else:
                print('数据库{}已经存在,执行该数据库操作'.format(db_name))
                # db = result[0]
                db = db_name
        except Exception as get_department_info_ERR:
            print('创建失败')
            print('get_department_info_ERR:', str(get_department_info_ERR))

        finally:
            connect.close()
        print(db)
        return db

    def create_db(self,db_name):
        """
        创建新的数据库

        :return:
        """
        select_sql ="select * from pg_database where datname='{}'".format(db_name)
        sql ="create database {} ".format(db_name)
        connect, cursor = self._default_connect()
        try:
            cursor.execute(select_sql)
            connect.commit()
            result = cursor.fetchall()
            if not result:
                connect.autocommit =True
                cursor.execute(sql)
                connect.autocommit =False
                print('创建成功')
            else:
                print('数据库{}已经存在'.format(db_name))

        except Exception as get_department_info_ERR:
            print('创建失败')
            print('get_department_info_ERR:', str(get_department_info_ERR))

        finally:
            connect.close()


    def get_all_database(self):
        """
        获取当前所有的database
        :return:
        """
        sql= "select datname From pg_database"
        connect, cursor = self._init_post_db()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            if result:
                for i in result:
                    print(i[0])
            else:
                print('没有database')
        except Exception as get_all_database_ERR:
            print(str(get_all_database_ERR))






    """
    -------------------------------------------------------------------------------
    schema操作
    -------------------------------------------------------------------------------
    """



    def select_schema(self):
        sql ="SELECT nspname FROM pg_namespace"

        connect, cursor = self._init_post_db()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            if result:
                for i in result:
                    print(i)
            else:
                print('没有schema')
        except Exception as select_schema_ERR:
            print(str(select_schema_ERR))


    def create_schema(self,schema_name):
        """
        创建schema
        :param db_name:
        :param schema_name:
        :return:
        """
        success = False
        sql ="create schema {}".format(schema_name)
        connect, cursor = self._init_post_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('schema创建成功')
        except Exception as creat_schema_ERR:
            print('creat_schema_ERR:',str(creat_schema_ERR))
        finally:
            connect.close()
        return success

    """
    -------------------------------------------------------------------------------
    创建table
    -------------------------------------------------------------------------------
    """
    def clearTableAndResetId(self,tablename):
        '''
        该函数情况数据表中的所有数据并重置递增id
        eg:  truncate table timeaxis.r_task restart identity cascade
        :param tablename:
        :return:
        '''
        success = False
        sql = "truncate table {} restart identity cascade".format(tablename)
        time_connect, time_cursor = self._init_post_db()
        try:
            time_cursor.execute(sql)
            time_connect.commit()
            success = True
            print('重置表成功')
        except Exception as clearTableAndResetId_ERR:
            print('clearTableAndResetId_ERR:',clearTableAndResetId_ERR)
        finally:
            time_connect.close()
        return success


    def create_projectHistory_table(self):
        """
        创建projectHistory表
        定期记录下当前日期，项目进度历史数据

        ***********************************************
            projectHistory表操作
            内容:记录项目进度内容，包括：时间，总分钟数,总个数，剩余分钟数，剩余个数,项目schema名称，项目中文名称
            字段:
                id : serial PRIMARY KEY
                date : character varying(50)
                eps: character varying(50)  集数名称
                sequence: character varying(50) 场次名称
                producer: character varying(200) 制作方
                allMinutes : int 总分钟数
                allCounts : int 总个数
                leftMinutes :int 剩余分钟数
                leftCounts :int 剩余个数
                database :character varying(50)  项目schema名称
                projectName :character varying(100)  项目中文名称
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.projectHistory(id serial PRIMARY KEY," \
              "date character varying(50)," \
              "eps character varying(50)," \
              "sequence character varying(50)," \
              "producer character varying(200)," \
              "allMinutes int," \
              "allCounts int," \
              "leftMinutes int," \
              "leftCounts int," \
              "database character varying(50), " \
              "projectName character varying(100))"

        connect, cursor = self._init_post_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('account表创建成功')
        except Exception as create_projectHistory_table:
            print('create_projectHistory_table:',str(create_projectHistory_table))
        finally:
            connect.close()
        return success

    def create_taskhistory_table(self):
        """
        创建taskHistory表
        每天早记录镜头数据

        ***********************************************
            taskHistory表操作
            内容:记录项目进度内容，包括：database,任务id，info_id(任务表中是info类型的镜头shot id),任务状态，账号id，
            artist,start_date,end_date,第一次任务提交时间，工时，镜头最终状态，镜头帧数
            字段:
                id : serial PRIMARY KEY
                database : character varying(50)  项目schema名称
                module : character varying(30)  任务类型
                task_id : character varying(100)  任务id
                info_id : character varying(100)  镜头id
                pipeline : character varying(50) 该任务的阶段
                assign_pipeline : text 该任务所在镜头的所有关联阶段
                status : character varying(50)  任务状态
                account_id : character varying(100) 账号id
                artist : character varying(200)  艺术家
                department : character varying(50)  部门
                start_date : character varying(50) 起始日期
                end_date : character varying(50) 结束日期
                first_submit_time : character varying(50) 第一次提交日期
                last_submit_time : character varying(50) 最后一次提交日期
                work_hour : character varying(50)  工时
                shot_final_status : character varying(50) 镜头最终状态
                frame : character varying(50) 镜头帧数
                fps : character varying(20) 帧速率

        ***********************************************

        :return:
        """
        success = False
        sql = "create table timeaxis.taskhistory(id serial PRIMARY KEY," \
              "database character varying(50),"\
              "module character varying(30),"\
              "task_id character varying(100),"\
              "info_id character varying(100),"\
              "pipeline character varying(50),"\
              "assign_pipeline text,"\
              "status character varying(50),"\
              "account_id character varying(100),"\
              "artist character varying(200),"\
              "department character varying(50),"\
              "start_date character varying(50),"\
              "end_date character varying(50),"\
              "first_submit_time character varying(50),"\
              "last_submit_time character varying(50),"\
              "work_hour character varying(50),"\
              "shot_final_status character varying(50),"\
              "frame character varying(50)," \
              "fps  character varying(20))"

        connect, cursor = self._init_post_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('taskhistoy表创建成功')
        except Exception as create_taskHistory_table_ERR:
            print('create_taskHistory_table_ERR:',str(create_taskHistory_table_ERR))
        finally:
            connect.close()
        return success

    def create_vender_history_table(self):
        """
        创建外包History表
        每月底记录一次当月所有制作方镜头数据

        ***********************************************
            taskHistory表操作
            内容:记录项目进度内容，包括：制作方,月初日期，项目database,项目全名,通过帧数,项目帧速率，
                该制作方各阶段信息
            字段:
                id : serial PRIMARY KEY
                vender : character varying(200) 外包方
                month_start_date : character varying(50)
                project_data : jsonb 项目数据
                department_data: jsonb  各部门数据
        ***********************************************
        :return:
        """
        success = False
        sql = "create table timeaxis.venderhistory(id serial PRIMARY KEY," \
              "vender character varying(200),"\
              "month_start_date character varying(50),"\
              "project_data jsonb,"\
              "pipeline_data jsonb)"

        connect, cursor = self._init_post_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('venderhistory表创建成功')
        except Exception as create_vender_history_table_ERR:
            print('create_vender_history_table_ERR:',str(create_vender_history_table_ERR))
        finally:
            connect.close()
        return success




    """
    -------------------------------------------------------------------------------
    table操作
    -------------------------------------------------------------------------------
    """


    def save_projectHistory(self,data):
        """
        保存项目历史进度记录
                date : character varying(50)
                eps: character varying(50)  集数名称
                sequence: character varying(50) 场次名称
                producer: character varying(200) 制作方
                allMinutes : int 总分钟数
                allCounts : int 总个数
                leftMinutes :int 剩余分钟数
                leftCounts :int 剩余个数
                database :character varying(50)  项目schema名称
                projectName :character varying(100)  项目中文名称
        :param data:
        :return:
        """
        if data:
            sql = "insert into timeaxis.projectHistory(date,eps,sequence,producer,allMinutes," \
                  "allCounts,leftMinutes,leftCounts,database,projectName) " \
                  f"values ('{data['date']}','{data['eps']}','{data['sequence']}','{data['producer']}',{data['allMinutes']}," \
                  f"{data['allCounts']},{data['leftMinutes']},{data['leftCounts']},'{data['database']}','{data['projectName']}')"

            time_connect, time_cursor = self._init_post_db()
            try:
                time_cursor.execute(sql)
                time_connect.commit()
            except Exception as save_projectHistory_ERR:
                print('save_projectHistory_ERR:',save_projectHistory_ERR)
            finally:
                time_connect.close()


    def get_projectHistory(self,database,eps,sequence,producer):
        """
        查看指定项目的集数进度历史信息
        :param database:
        :param eps: 集数
        :param sequence: 场数
        :param producer: 制作方
        :return:
        """
        sql = f"select date,allMinutes,allCounts,leftMinutes,leftCounts " \
              f"from timeaxis.projectHistory where database = '{database}' and eps ='{eps}' and sequence = '{sequence}' " \
              f"and producer = '{producer}'"

        time_connect, time_cursor = self._init_post_db()
        back = {'data':[]}
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                key = 0
                for i in result:
                    data = {}
                    date, allMinutes, allCounts, leftMinutes, leftCounts = i
                    data['key'] = key
                    data['time'] = date
                    data['minuteAll'] = allMinutes
                    data['countAll'] = allCounts
                    data['minuteResidue'] = leftMinutes
                    data['countResidue'] = leftCounts
                    back['data'].append(data)
                    key += 1

        except Exception as get_projectHistory_ERR:
            print('get_projectHistory_ERR:', get_projectHistory_ERR)
        finally:
            time_connect.close()

        return back




    def save_taskHistory(self,data_list):
        """
        保存数据
               id : serial PRIMARY KEY
                database : character varying(50)  项目schema名称
                module : character varying(30)  项目schema名称
                task_id : character varying(100)  任务id
                info_id : character varying(100)  镜头id
                pipeline : character varying(50) 该任务的阶段
                assign_pipeline : text 该任务所在镜头的所有关联阶段
                status : character varying(50)  任务状态
                account_id : character varying(100) 账号id
                artist : character varying(200)  艺术家
                department : character varying(50)  部门
                start_date : character varying(50) 起始日期
                end_date : character varying(50) 结束日期
                first_submit_time : character varying(50) 第一次提交日期
                last_submit_time : character varying(50) 最后一次提交日期
                work_hour : character varying(50)  工时
                shot_final_status : character varying(50) 镜头最终状态
                frame : character varying(50) 镜头帧数
                fps : character varying(20) 帧速率
        :param data_list:
        :return:
        """

        if data_list:
            time_connect, time_cursor = self._init_post_db()
            try:
                for data in data_list:

                    sql = "insert into timeaxis.taskhistory(database,module,task_id,info_id,pipeline," \
                          "assign_pipeline,status,account_id,department,artist," \
                          "start_date,end_date,first_submit_time,last_submit_time,work_hour," \
                          "shot_final_status,frame,fps) " \
                          f"values ('{data['database']}','{data['module']}','{data['task_id']}','{data['info_id']}','{data['pipeline']}'," \
                          f"'{data['assign_pipeline']}','{data['status']}','{data['account_id']}', '{data['department']}','{data['artist']}'," \
                          f"'{data['start_date']}','{data['end_date']}','{data['first_submit_time']}','{data['last_submit_time']}','{data['work_hour']}'," \
                          f"'{data['shot_final_status']}','{data['frame']}','{data['fps']}')"
                    time_cursor.execute(sql)
                time_connect.commit()
            except Exception as save_taskHistory_ERR:
                print('save_taskHistory_ERR:',save_taskHistory_ERR)
            finally:
                time_connect.close()

    def get_taskHistory_by_weeks(self,weeks):
        """
        通过周列表获取任务历史
        :param weeks:
        :return:
        """
        wtasks = []
        time_connect, time_cursor = self._init_post_db()
        try:
            for week in weeks:
                connect_days = '|'.join(week)
                # print('connect_days:',connect_days)
                task_list = []
                sql = "select id,module,status,account_id,department,start_date," \
                      "end_date,first_submit_time,last_submit_time,work_hour," \
                      "shot_final_status,frame,pipeline,assign_pipeline,fps from timeaxis.taskHistory where " \
                      f"end_date similar to '({connect_days})'"
                time_cursor.execute(sql)
                result = time_cursor.fetchall()
                if result:
                    for i in result:
                        data = {}
                        data['id'],data['module'],data['status'],data['account_id'],data['department'],data['start_date'],\
                        data['end_date'],data['first_submit_time'],data['last_submit_time'],data['work_hour'],data['shot_final_status'],\
                        data['frame'],data['pipeline'],data['assign_pipeline'],data['fps'] = i
                        task_list.append(data)
                wtasks.append(task_list)
                # print(connect_days,len(task_list))
        except Exception as get_taskHistory_by_weeks_ERR:
            print('get_taskHistory_by_weeks_ERR:',get_taskHistory_by_weeks_ERR)

        finally:
            time_connect.close()
        # print('总长度:',len(wtasks))
        return wtasks

    def _intersection_days(self,_start_date,_end_date,start_date,end_date):
        """
        求相交日期
        :param _start_date: 选择的起始日期
        :param _end_date:   选择的结束日期
        :param start_date:  任务的起始日期
        :param end_date:    任务的结束日期
        :return:
        """
        days = 0
        if start_date and end_date:
            _dates = self.mzh.getAlldaysInStartEndDay(_start_date,_end_date)
            dates = self.mzh.getAlldaysInStartEndDay(start_date,end_date)
            for d in dates:
                if d in _dates:
                    days += 1

        return days

    def get_taskHistory_by_start_end_date(self,_start_date,_end_date):
        """
        通过任务的起始与结束日期来获取日期范围触及到的任务，包括：
        1. 任务start_date<= _start_date , 任务_start_date <=end_date <=_end_date
        2. 任务start_date>= _start_date , 任务_start_date <=end_date <=_end_date
        2. 任务start_date<= _end_date , 任务_end_date >=_end_date
        综合条件： 任务end_date>=start_date and 任务start_date <= end_date
        :param weeks:
        :return:
        """
        task_list = []
        time_connect, time_cursor = self._init_post_db()
        try:

            sql = "select status,account_id,artist,department,start_date," \
                  "end_date,first_submit_time,last_submit_time,work_hour,shot_final_status," \
                  "frame,pipeline,assign_pipeline,fps from timeaxis.taskHistory where " \
                  f"end_date >='{_start_date}' and start_date<='{_end_date}'"
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                for i in result:
                    data = {}
                    data['status'],data['account_id'],data['artist'],data['department'],data['start_date'],\
                    data['end_date'],data['first_submit_time'],data['last_submit_time'],data['work_hour'],data['shot_final_status'],\
                    data['frame'],data['pipeline'],data['assign_pipeline'],data['fps'] = i
                    work_hour = float(data['work_hour']) if data['work_hour'] not in ['None',''] else 0.0
                    perday_workhour = float(work_hour)/(self.mzh.delta_date(data['start_date'],data['end_date'])+1)
                    effective_days = self._intersection_days(_start_date,_end_date,data['start_date'],data['end_date'])
                    effective_hours = round(perday_workhour*effective_days,2)
                    data['perday_workhour'] = perday_workhour
                    data['effective_days'] = effective_days
                    data['effective_hours'] = effective_hours
                    task_list.append(data)

        except Exception as get_taskHistory_by_weeks_ERR:
            print('get_taskHistory_by_weeks_ERR:',get_taskHistory_by_weeks_ERR)

        finally:
            time_connect.close()

        return task_list


    def save_venderHistory(self,all_data):
        """
        保存制作方数据,保存的为该月，各制作方的各项目的数据。不会存各制作方各项目的重复.
        但是因为department_data会每个项目重复一次重复！！！注意，department_data是每个月的各阶段数据，最后显示是需要各个月各部门全部+起来
        :param all_data:{{'Vender 山东': {'month_start_date': '2021-09-01',
                                        'project_data': {'山河表里': 0.057, '两个人的小森林': 0, '皓衣行': 27.198999999999963,
                                                        '九指神丐2': 0.5770000000000001, '明日之后_BJ': 0, '忽必烈': 0, '女魃烛龙': 0,
                                                        '人生大事_BJ': 0, '沉香如屑': 0.135, '重紫': 0, '夺宝鲲宫': 2.2139999999999995,
                                                        '月刊少女': 24.834},
                                        'pipeline_data': {'mm03': 48, 'ani03_c': 13, 'roto03': 343, 'paint03': 553,
                                                            'cmp03': 490, 'ani03': 14, 'lgt03': 2, 'tex03': 2, 'mod03': 2, 'mp03': 3}},
                     'xxx': {...},
                     'xxx':{...}
                     }
        :return:
        """
        if all_data:
            time_connect, time_cursor = self._init_post_db()
            try:
                for vender,data in all_data.items():
                    month_start_date = data.get('month_start_date')
                    project_data = data.get('project_data')
                    pipeline_data = data.get('pipeline_data')
                    sql = "insert into timeaxis.venderhistory(vender,month_start_date,project_data,pipeline_data) " \
                          f"values ('{vender}','{month_start_date}','{json.dumps(project_data)}','{json.dumps(pipeline_data)}')"
                    time_cursor.execute(sql)
                time_connect.commit()
            except Exception as save_venderHistory_ERR:
                print('save_venderHistory_ERR:',save_venderHistory_ERR)
            finally:
                time_connect.close()


    def get_venderHistory_by_month_start_date_list(self,month_start_date_list):
        """
        通过月初日期来获取外包历史数据
        :return:
        """
        connect_dates = '|'.join(month_start_date_list)
        sql = "select vender,month_start_date,project_data,pipeline_data from timeaxis.venderhistory " \
              f"where month_start_date similar to '({connect_dates})'"
        time_connect, time_cursor = self._init_post_db()
        data_list = []
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                for i in result:
                    data = {}
                    data['vender'], data['month_start_date'], data['project_data'], data['pipeline_data'] = i
                    data_list.append(data)
            time_connect.commit()
        except Exception as get_venderHistory_by_month_start_date_list_ERR:
            print('get_venderHistory_by_month_start_date_list_ERR:', get_venderHistory_by_month_start_date_list_ERR)
        finally:
            time_connect.close()

        return data_list


    def get_venderHistory_by_month_start_date(self,month_start_date):
        """
        根据提供的单月日期来获取数据，方便每月统计
        :param month_start_date:
        :return:
        """
        sql = "select vender,month_start_date,project_data,pipeline_data from timeaxis.venderhistory " \
              f"where month_start_date = '{month_start_date}'"
        time_connect, time_cursor = self._init_post_db()
        data_list = []
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                for i in result:
                    data = {}
                    data['vender'], data['month_start_date'], data['project_data'], data['pipeline_data'] = i
                    data_list.append(data)
            time_connect.commit()
        except Exception as get_venderHistory_by_month_start_date_ERR:
            print('get_venderHistory_by_month_start_date_ERR:', get_venderHistory_by_month_start_date_ERR)
        finally:
            time_connect.close()

        return data_list
